package unilearn.da;

import java.sql.*;
import java.util.Vector;

import unilearn.domain.Subject;


public class DASubject extends DABase
{
	
	public DASubject()
	{ }
	
	public static long insertSubject(Subject sub) throws SQLException
	{
		long subID = 0;
		PreparedStatement ps = null;
		
		try
		{
			Connection conn = getConn();
			
			ps = conn.prepareStatement(
			"INSERT INTO SUBJECT (subject_code,subject_name,semester,year,description,convenor_name,convenor_email) "
							+ "VALUES (?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
							
			ps.setString(1, sub.getSubjectCode());
			ps.setString(2, sub.getSubjectName());
			ps.setInt(3, sub.getSemester());
			ps.setInt(4, sub.getYear());
			ps.setString(5, sub.getDescription());
			ps.setString(6, sub.getConvenorName());
			ps.setString(7, sub.getConvenorEmail());
			
			ps.execute();
			
			ResultSet results = ps.getGeneratedKeys();
			if (results.next())
			{
				subID = results.getLong(1);
			}
		}
		finally
		{
			ps.close();
		}

		return subID;
	}
	
	public static void updateSubject(long subID, Subject sub) throws SQLException
	{
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("UPDATE SUBJECT SET subject_code = ?, subject_name = ?, semester = ?, "
					+ "year = ?, description = ?, convenor_name = ? , convenor_email = ? WHERE subject_id = ?");

			ps.setString(1, sub.getSubjectCode());
			ps.setString(2, sub.getSubjectName());
			ps.setInt(3, sub.getSemester());
			ps.setInt(4, sub.getYear());
			ps.setString(5, sub.getDescription());
			ps.setString(6, sub.getConvenorName());
			ps.setString(7, sub.getConvenorEmail());
			ps.setLong(8, subID);

			ps.execute();

		}
		finally
		{
			ps.close();
		}
	}
	
	public static void delete(long subID) throws SQLException
	{
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("DELETE FROM SUBJECT WHERE subject_id = ?");
			ps.setLong(1, subID);

			ps.execute();
		}
		finally
		{
			ps.close();
		}
		
	}
	
	public static Vector<Subject> getAllSubjects() throws SQLException
	{
		Vector<Subject> sub = new Vector<Subject>();
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("select * from subject");

			ResultSet rs = ps.executeQuery();

			while (rs.next())
			{
				sub.add(getSubjectByID(rs.getLong("subjectID")));
			}
		}
		finally
		{
			ps.close();
		}

		return sub;
	}
	
	public static Subject getSubjectByCode(String subjectCode) throws SQLException
	{ 
		Subject sub = null;
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("select * from subject where subject_code = ?");
			ps.setString(1, subjectCode);

			ResultSet rs = ps.executeQuery();

			if (rs.next())
			{
				sub = new Subject(rs.getLong("subject_id"));

				sub.setSubjectCode(rs.getString("subject_code"));
				sub.setSubjectName(rs.getString("subject_name"));
				sub.setSemester(rs.getInt("semester"));
				sub.setYear(rs.getInt("year"));
				sub.setDescription(rs.getString("description"));
				sub.setConvenorName(rs.getString("convenor_name"));
				sub.setConvenorEmail(rs.getString("convenor_email"));
			}

		}
		finally
		{
			ps.close();
		}

		return sub; }
	
	public static Subject getSubjectByID(long subjectID) throws SQLException
	{ 
		Subject sub = null;
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("select * from subject where subject_id = ?");
			ps.setLong(1, subjectID);

			ResultSet rs = ps.executeQuery();

			if (rs.next())
			{
				sub = new Subject(rs.getLong("subject_id"));

				sub.setSubjectCode(rs.getString("subject_code"));
				sub.setSubjectName(rs.getString("subject_name"));
				sub.setSemester(rs.getInt("semester"));
				sub.setYear(rs.getInt("year"));
				sub.setDescription(rs.getString("description"));
				sub.setConvenorName(rs.getString("convenor_name"));
				sub.setConvenorEmail(rs.getString("convenor_email"));
			}

		}
		finally
		{
			ps.close();
		}

		return sub;
	}
	
	public static Subject select(long subID) throws SQLException
	{
		return getSubjectByID(subID);
	}
	
	
	
	
	
}
